﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.zhongjh.Library.Common;
using MySql.Data.MySqlClient;
using Oracle.DataAccess.Client;

namespace com.zhongjh.Library.Database
{
    public class DbSvc : IDbSvc
    {
        #region 字段

        internal DbInfoEntity m_DbInfo = null;

        //数据库对象
        private IDbConnection m_Connection = null;
        private IDbCommand m_Command = null;
        private IDbDataAdapter m_DataAdapter = null;
        private IDbTransaction m_Transaction = null;
        //private SqlDataReader m_DataReader = null;

        //日志
        private Log m_Log = new Log(InnerCommon.m_strLogPath);

        #endregion //end 字段


        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="p_DbInfo"></param>
        public DbSvc(ref DbInfoEntity p_DbInfo)
        {
            if (p_DbInfo.DbConfigKey.Equals(string.Empty))
                p_DbInfo = DbConfigManager.GetDbInfo("DbConfig");
            else
                p_DbInfo = DbConfigManager.GetDbInfo(p_DbInfo.DbConfigKey);

            CreateDbObject(ref p_DbInfo);
            m_DbInfo = p_DbInfo;
        }


        #endregion //end 构造函数


        #region 公共方法

        /// <summary>
        /// 查询数据库数据(没有参数)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_dtResult"></param>
        /// <returns></returns>
        public bool QueryDbDataNoParameter(string p_strSql, ref DataTable p_dtResult)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                DataSet dsResult = new DataSet();
                m_DataAdapter.Fill(dsResult);
                if (dsResult.Tables.Count > 0)
                    p_dtResult = dsResult.Tables[0].Copy();

                result = true;

                //写日志
                WriteDbLog(p_strSql);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "查询数据库数据(没有参数)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 修改数据库数据(没有参数)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <returns></returns>
        public bool ModifyDbDataNoParameter(string p_strSql)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                if (m_Transaction != null)
                    m_Command.Transaction = m_Transaction;
                m_Command.ExecuteNonQuery();

                result = true;

                //写日志
                WriteDbLog(p_strSql);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "修改数据库数据(没有参数)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 查询数据库数据(有参数)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_DbParameter"></param>
        /// <param name="p_dtResult"></param>
        /// <returns></returns>
        public bool QueryDbDataHasParameter(string p_strSql, IDataParameter[] p_idpDbParameter, ref DataTable p_dtResult)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                for (int i1 = 0; i1 < p_idpDbParameter.Length; i1++)
                {
                    m_Command.Parameters.Add(p_idpDbParameter[i1]);
                }
                DataSet dsResult = new DataSet();
                m_DataAdapter.Fill(dsResult);
                if (dsResult.Tables.Count > 0)
                    p_dtResult = dsResult.Tables[0].Copy();

                result = true;

                //写日志
                WriteDbLog(p_strSql, p_idpDbParameter);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "查询数据库数据(有参数)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 修改数据库数据(有参数)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_DbParameter"></param>
        /// <returns></returns>
        public bool ModifyDbDataHasParameter(string p_strSql, IDataParameter[] p_idpDbParameter)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                for (int i1 = 0; i1 < p_idpDbParameter.Length; i1++)
                {
                    m_Command.Parameters.Add(p_idpDbParameter[i1]);
                }
                m_Command.ExecuteNonQuery();

                result = true;

                //写日志
                WriteDbLog(p_strSql, p_idpDbParameter);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "修改数据库数据(有参数)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 查询数据库数据(没有参数)(存储过程)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_Result"></param>
        /// <returns></returns>
        public bool QueryDBDataNoParameter_SP(string p_strSql, ref DataTable p_dtResult)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.Parameters.Clear();
                m_Command.CommandText = p_strSql;
                m_Command.CommandType = CommandType.StoredProcedure;
                DataSet dsResult = new DataSet();
                m_DataAdapter.Fill(dsResult);
                if (dsResult.Tables.Count > 0)
                    p_dtResult = dsResult.Tables[0].Copy();

                result = true;

                //写日志
                WriteDbLog(p_strSql);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "查询数据库数据(没有参数)(存储过程)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 修改数据库数据(没有参数)(存储过程)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <returns></returns>
        public bool ModifyDbDataNoParameter_SP(string p_strSql)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.Parameters.Clear();
                m_Command.CommandText = p_strSql;
                m_Command.CommandType = CommandType.StoredProcedure;
                m_Command.ExecuteNonQuery();

                result = true;

                //写日志
                WriteDbLog(p_strSql);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "修改数据库数据(没有参数)(存储过程)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 查询数据库数据(有参数)(存储过程)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_DbParameter"></param>
        /// <param name="p_Result"></param>
        /// <returns></returns>
        public bool QueryDBDataHasParameter_SP(string p_strSql, IDataParameter[] p_idpDbParameter, ref DataTable p_dtResult)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                for (int i1 = 0; i1 < p_idpDbParameter.Length; i1++)
                {
                    m_Command.Parameters.Add(p_idpDbParameter[i1]);
                }
                m_Command.CommandType = CommandType.StoredProcedure;
                DataSet dsResult = new DataSet();
                m_DataAdapter.Fill(dsResult);
                if (dsResult.Tables.Count > 0)
                    p_dtResult = dsResult.Tables[0].Copy();

                result = true;

                //写日志
                WriteDbLog(p_strSql, p_idpDbParameter);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "查询数据库数据(有参数)(存储过程)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 修改数据库数据(有参数)(存储过程)
        /// </summary>
        /// <param name="p_strSql"></param>
        /// <param name="p_DbParameter"></param>
        /// <returns></returns>
        public bool ModifyDbDataHasParameter_SP(string p_strSql, IDataParameter[] p_idpDbParameter)
        {
            bool result = false;

            try
            {
                if (!IsOpenConnection())
                    m_Connection.Open();

                m_Command.CommandText = p_strSql;
                for (int i1 = 0; i1 < p_idpDbParameter.Length; i1++)
                {
                    m_Command.Parameters.Add(p_idpDbParameter[i1]);
                }
                m_Command.CommandType = CommandType.StoredProcedure;
                m_Command.ExecuteNonQuery();

                result = true;

                //写日志
                WriteDbLog(p_strSql, p_idpDbParameter);
            }
            catch (Exception ex)
            {
                Error.AppendException(ex, "修改数据库数据(有参数)(存储过程)");
                result = false;
            }

            return result;
        }


        /// <summary>
        /// 批量保存
        /// </summary>
        /// <param name="p_dsData"></param>
        /// <returns></returns>
        public bool BanthSave(DataSet p_dsData)
        {
            return true; //m_DbSvc.BanthSave(p_dsData);
        }



        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (!IsOpenConnection())
                m_Connection.Open();
            m_Transaction = m_Connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            m_Transaction.Commit();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            m_Transaction.Rollback();
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseConnection()
        {
            m_Connection.Close();
        }

        /// <summary>
        /// 创建数据参数
        /// </summary>
        /// <param name="p_intLength"></param>
        /// <returns></returns>
        public IDataParameter[] CreateDataParameter(int p_intLength)
        {
            IDataParameter[] result = null;
            //SqlParameter[] sqlParameter = new SqlParameter[p_intLength];

            if (m_Command != null)
                m_Command.Parameters.Clear();

            if (m_DbInfo.DbType == EDbType.Access)
                result = new OleDbParameter[p_intLength];
            else if (m_DbInfo.DbType == EDbType.MySql)
                result = new MySqlParameter[p_intLength];
            else if (m_DbInfo.DbType == EDbType.Oracle)
                result = new OracleParameter[p_intLength];
            else if (m_DbInfo.DbType == EDbType.Sql)
                result = new SqlParameter[p_intLength];

            for (int i1 = 0; i1 < p_intLength; i1++)
            {
                IDataParameter dataParameter = null;
                if (m_Command != null)
                    dataParameter = m_Command.CreateParameter();
                result[i1] = dataParameter;
            }

            return result;
        }

        /// <summary>
        /// 获取序号
        /// </summary>
        /// <param name="p_lngCode"></param>
        /// <returns></returns>
        public long GetSN(long p_lngCode)
        {
            long result = 0;
            long lngSn = 0;
            string strSql = string.Empty;
            DataTable dtbResult = new DataTable();

            if (m_DbInfo.DbType == EDbType.Sql)
            {
                strSql = "proc_GetAutoSn";
                IDataParameter[] objDbPare = CreateDataParameter(2);
                objDbPare[0].ParameterName = "@" + CAutoSnField.m_Code;
                objDbPare[0].Value = p_lngCode;
                objDbPare[1].Direction = ParameterDirection.Output;
                objDbPare[1].ParameterName = "@" + CAutoSnField.m_Sn;
                objDbPare[1].Value = result;
                DataTable dtTemp = new DataTable();
                if (QueryDBDataHasParameter_SP(strSql, objDbPare, ref dtTemp))
                {
                    long.TryParse(objDbPare[1].Value.ToString(), out result);
                }
            }
            else
            {
                strSql = "select " + CAutoSnField.m_Code + ", " + CAutoSnField.m_Sn + " from " + CAutoSnField.TableName + " where " + CAutoSnField.m_Code + " = " + p_lngCode.ToString();
                if (QueryDbDataNoParameter(strSql, ref dtbResult))
                {
                    if (dtbResult.Rows.Count > 0)
                    {
                        string update = string.Empty;
                        lngSn = long.Parse(dtbResult.Rows[0][CAutoSnField.m_Sn].ToString()) + 1;
                        update = "update " + CAutoSnField.TableName + " set " + CAutoSnField.m_Sn + " = " + lngSn.ToString() + " where " + CAutoSnField.m_Code + " = " + p_lngCode.ToString();
                        if (ModifyDbDataNoParameter(update))
                        {
                            result = lngSn;
                        }
                        else
                        {
                            result = -1;
                        }
                    }
                    else
                    {
                        string insert = string.Empty;
                        lngSn = result + 1;
                        insert = "insert into " + CAutoSnField.m_Code + ", " + CAutoSnField.m_Sn + " from " + CAutoSnField.TableName;
                        if (ModifyDbDataNoParameter(insert))
                        {
                            result = lngSn;
                        }
                        else
                        {
                            result = -1;
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 回收序号
        /// </summary>
        /// <param name="p_lngCode"></param>
        /// <param name="p_lngSn"></param>
        /// <returns></returns>
        public bool RecSN(long p_lngCode, long p_lngRecSn)
        {
            bool result = false;
            string strSelectSn = string.Empty;
            string strSeletcSnRec = string.Empty;
            string strUpdateSn = string.Empty;
            string strInsertSnRec = string.Empty;
            DataTable dtSn = new DataTable();
            DataTable dtSnRec = new DataTable();

            strSelectSn = "select " + CAutoSnField.m_Code + "," + CAutoSnField.m_Sn + " from " + CAutoSnField.TableName + " where " + CAutoSnField.m_Code + " = " + p_lngCode.ToString();
            strSeletcSnRec = "select " + CAutoSnRecField.m_Code + "," + CAutoSnRecField.m_RecSn + " from " + CAutoSnRecField.TableName + " where " + CAutoSnRecField.m_Code + " = " + p_lngCode.ToString() + " and " + CAutoSnRecField.m_RecSn + " = " + p_lngRecSn.ToString();
            if (QueryDbDataNoParameter(strSelectSn, ref dtSn) && QueryDbDataNoParameter(strSeletcSnRec, ref dtSnRec))
            {
                if (dtSn.Rows.Count > 0)
                {
                    byte bytHasRec = 0;
                    byte.TryParse(dtSn.Rows[0][CAutoSnField.m_HadRec].ToString(), out bytHasRec);

                    try
                    {
                        BeginTransaction();
                        if (bytHasRec == 0)
                        {
                            strUpdateSn = "update " + CAutoSnField.TableName + " set " + CAutoSnField.m_HadRec + " = 1 where " + CAutoSnField.m_Code + " = " + p_lngCode.ToString();
                            result = ModifyDbDataNoParameter(strUpdateSn);
                        }

                        if (dtSnRec.Rows.Count == 0)
                        {
                            strInsertSnRec = "insert into " + CAutoSnRecField.TableName + " (" + CAutoSnRecField.m_Code + "," + CAutoSnRecField.m_RecSn + ") + values(" + p_lngCode.ToString() + "," + p_lngRecSn.ToString() + ")";
                            result = ModifyDbDataNoParameter(strInsertSnRec);
                        }
                        else
                        {
                            result = false;
                        }

                        CommitTransaction();
                    }
                    catch (Exception)
                    {
                        RollbackTransaction();
                        result = false;
                    }
                }
                else
                {
                    result = false;
                }
            }
            else
            {
                result = false;
            }

            return result;
        }

        #endregion //公共方法


        #region 私有方法

        /// <summary>
        /// 创建数据库访对象
        /// </summary>
        /// <param name="p_DbInfo"></param>
        private void CreateDbObject(ref DbInfoEntity p_DbInfo)
        {
            EDbType m_DbType = p_DbInfo.DbType;

            switch (m_DbType)
            {
                case EDbType.Access:
                    m_Connection = new OleDbConnection(p_DbInfo.ConnectionString); ;
                    m_Command = m_Command = m_Connection.CreateCommand(); ;
                    m_DataAdapter = new OleDbDataAdapter((OleDbCommand)m_Command);
                    break;
                case EDbType.Sql:
                    m_Connection = new SqlConnection(p_DbInfo.ConnectionString); ;
                    m_Command = m_Command = m_Connection.CreateCommand(); ;
                    m_DataAdapter = new SqlDataAdapter((SqlCommand)m_Command);
                    break;
                case EDbType.MySql:
                    m_Connection = new MySqlConnection(p_DbInfo.ConnectionString); ;
                    m_Command = m_Command = m_Connection.CreateCommand(); ;
                    m_DataAdapter = new MySqlDataAdapter((MySqlCommand)m_Command);
                    break;
                case EDbType.Oracle:
                    m_Connection = new OracleConnection(p_DbInfo.ConnectionString); ;
                    m_Command = m_Command = m_Connection.CreateCommand(); ;
                    m_DataAdapter = new OracleDataAdapter((OracleCommand)m_Command);
                    break;
            }
        }

        private bool IsOpenConnection()
        {
            bool result = false;
            if (m_Connection.State == ConnectionState.Open)
                result = true;
            return result;
        }

        private void WriteDbLog(string p_strSql, IDataParameter[] p_DbParameter)
        {
            if (m_DbInfo.IsLog == true)
            {
                StringBuilder sbLog = new StringBuilder();
                sbLog.Append(p_strSql + " (");
                for (int i1 = 0; i1 < p_DbParameter.Length; i1++)
                {
                    sbLog.Append(p_DbParameter[i1].ParameterName + "=" + p_DbParameter[i1].Value);
                    if (i1 < (p_DbParameter.Length - 1))
                        sbLog.Append(", ");
                }
                sbLog.Append(")");
                m_Log.WriteLog(sbLog.ToString());
            }
        }

        private void WriteDbLog(string p_strSql)
        {
            if (m_DbInfo.IsLog == true)
            {
                m_Log.WriteLog(p_strSql);
            }
        }

        #endregion //end 私有方法
    }
}
